﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
namespace DAO
{
    public class MonAnDAO
    {
        public MonAnDTO LayThongTinMonAn(string MaNV)
        {
            MonAnDTO ma = new MonAnDTO();
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();

            SqlCommand cmd = new SqlCommand("proc_LayMonTheoMa", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;
            sqlPar = new SqlParameter("@mama", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = MaNV;
            cmd.Parameters.Add(sqlPar);
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            ma.MaMA = reader.GetString(0);
            ma.TenMA = reader.GetString(1);
            ma.LoaiMA = reader.GetString(2);
           // ma.MaKM = reader.GetString(3);
            ma.SoLuong = reader.GetInt32(4);
            conn.Close();
            return ma;
        }
        public List<MonAnDTO> DanhSachMonAn(string MaLMA)
        {
            List<MonAnDTO> ds = new List<MonAnDTO>();
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();

            SqlCommand cmd = new SqlCommand("proc_DanhSachMonAn", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;
            sqlPar = new SqlParameter("@malma", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = MaLMA;
            cmd.Parameters.Add(sqlPar);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                MonAnDTO ma = new MonAnDTO();
                ma.MaMA = reader.GetString(0);
                ma.TenMA = reader.GetString(1);
                ma.LoaiMA = reader.GetString(2);
                //ma.MaKM = reader.GetString(3);
                ma.SoLuong = reader.GetInt32(4);
                ds.Add(ma);
            }
            conn.Close();
            return ds;
        }

        public int UpdateSLMA(string mama,int soluong)
        {
            int res;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("usp_UpdateSLMA", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;

            sqlPar = new SqlParameter("@mama", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = mama;
            cmd.Parameters.Add(sqlPar);
            
            sqlPar = new SqlParameter("@sldat", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = soluong;
            cmd.Parameters.Add(sqlPar);           

            sqlPar = new SqlParameter("@res", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(sqlPar);

            res = cmd.ExecuteNonQuery();
            res = int.Parse(cmd.Parameters["@res"].Value.ToString());
            conn.Close();
            return res;
        }

        public int LaySLMA(string mama)
        {
            int sl=0;
            string sql = "SELECT SLMA FROM MONAN WHERE MaMA='" + mama + "'";
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            reader.Read();            
            sl= reader.GetInt32(0);
            conn.Close();
            return sl;
        }

        public int UpdateSLMAsaukhiHuy(String Madh)
        {
            int res;
            SqlConnection conn = new SqlConnection();
            conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("usp_HuyDH_UpdateSLMA", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sqlPar;

            sqlPar = new SqlParameter("@madh", SqlDbType.VarChar, 6);
            sqlPar.Direction = ParameterDirection.Input;
            sqlPar.Value = Madh;
            cmd.Parameters.Add(sqlPar);

            sqlPar = new SqlParameter("@res", SqlDbType.Int);
            sqlPar.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(sqlPar);

            res = cmd.ExecuteNonQuery();
            res = int.Parse(cmd.Parameters["@res"].Value.ToString());
            conn.Close();
            return res;

        }
    }
}
